<html>
<!-- =====================================================================

  File:      Generated file for Adventure Works Cycles Storefront Sample
  Summary:   Self-documentation for application
  Date:	     June 16, 2003

=====================================================================

  This file is part of the Microsoft SQL Server Code Samples.
  Copyright (C) 2003 Microsoft Corporation.  All rights reserved.

This source code is intended only as a supplement to Microsoft
Development Tools and/or on-line documentation.  See these other
materials for detailed information regarding Microsoft code samples.

THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.

======================================================= -->
<head>
  <link rel=stylesheet href=style.css>
</head>
<body>
<div class=SourcePanel style="font-size:12">
<pre style="background-color:white">
<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #2040a0">System</span><span style="color: #4444FF">;</span>
<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #2040a0">System</span>.<span style="color: #2040a0">Data</span><span style="color: #4444FF">;</span>
<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #2040a0">System</span>.<span style="color: #2040a0">Data</span>.<span style="color: #2040a0">Sql</span><span style="color: #4444FF">;</span>
<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #2040a0">System</span>.<span style="color: #2040a0">Data</span>.<span style="color: #2040a0">SqlTypes</span><span style="color: #4444FF">;</span>
<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #2040a0">System</span>.<span style="color: #2040a0">Data</span>.<span style="color: #2040a0">SqlServer</span><span style="color: #4444FF">;</span>
<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #2040a0">System</span>.<span style="color: #2040a0">Globalization</span><span style="color: #4444FF">;</span>
<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #2040a0">Microsoft</span>.<span style="color: #2040a0">Samples</span>.<span style="color: #2040a0">SqlServer</span><span style="color: #4444FF">;</span>
<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #2040a0">System</span>.<span style="color: #2040a0">IO</span><span style="color: #4444FF">;</span>

<span style="color: #008000">/*=====================================================================

  File:      CurrencyConverter.cs for Adventure Works Cycles SQLCLR Layer Sample
  Summary:   Defines the ConvertCurrency method which is exposed as a UDF in SQL Server.
  Date:	     August 15, 2003

---------------------------------------------------------------------

  This file is part of the Microsoft SQL Server Code Samples.
  Copyright (C) Microsoft Corporation.  All rights reserved.

This source code is intended only as a supplement to Microsoft
Development Tools and/or on-line documentation.  See these other
materials for detailed information regarding Microsoft code samples.

THIS CODE AND INFORMATION ARE PROVIDED &quot;AS IS&quot; WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.

======================================================= */</span>

<span style="color: #0000FF"><strong>namespace</strong></span> <span style="color: #2040a0">Microsoft</span>.<span style="color: #2040a0">Samples</span>.<span style="color: #2040a0">SqlServer</span>
<span style="color: #4444FF"><strong>{</strong></span>
	<span style="color: #008000">/// &lt;summary&gt;</span>
	<span style="color: #008000">/// This class is used to compute the value of US money a given region.</span>
	<span style="color: #008000">/// &lt;/summary&gt;</span>
	<span style="color: #0000FF"><strong>public</strong></span> <span style="color: #2040a0">sealed</span> <span style="color: #0000FF"><strong>class</strong></span> <span style="color: #2040a0">CurrencyConverter</span>
	<span style="color: #4444FF"><strong>{</strong></span>
		<span style="color: #008000">// Classes with only static members should not be instantiable</span>
		<span style="color: #0000FF"><strong>private</strong></span> <span style="color: #2040a0">CurrencyConverter</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span>
		<span style="color: #4444FF"><strong>{</strong></span>
		<span style="color: #4444FF"><strong>}</strong></span>
		<span style="color: #0000FF"><strong>private</strong></span> <span style="color: #0000FF"><strong>static</strong></span> <span style="color: #2040a0">readonly</span> <span style="color: #2040a0">CultureInfo</span> <span style="color: #2040a0">USCulture</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">CultureInfo</span>.<span style="color: #2040a0">CreateSpecificCulture</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;en-us&quot;</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
		<span style="color: #008000">/// &lt;summary&gt;</span>
		<span style="color: #008000">///		Computes the value of a certain amount of money in the USA in a different region.</span>
		<span style="color: #008000">/// &lt;/summary&gt;</span>
		<span style="color: #008000">/// &lt;param name=&quot;fromAmount&quot;&gt;The quantity of money&lt;/param&gt;</span>
		<span style="color: #008000">/// &lt;param name=&quot;toCultureName&quot;&gt;A culture which is a member of the region of interest&lt;/param&gt;</span>
		<span style="color: #008000">/// &lt;returns&gt;&lt;/returns&gt;</span>
		<span style="color: #4444FF">[</span><span style="color: #2040a0">SqlFunction</span><span style="color: #4444FF">(</span><span style="color: #2040a0">DataAccess</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">DataAccessKind</span>.<span style="color: #2040a0">Read</span><span style="color: #4444FF">)</span><span style="color: #4444FF">]</span>
		<span style="color: #4444FF">[</span><span style="color: #2040a0">CLSCompliant</span><span style="color: #4444FF">(</span><span style="color: #0000FF"><strong>false</strong></span><span style="color: #4444FF">)</span><span style="color: #4444FF">]</span>
		<span style="color: #0000FF"><strong>public</strong></span> <span style="color: #0000FF"><strong>static</strong></span> <span style="color: #2040a0">Microsoft</span>.<span style="color: #2040a0">Samples</span>.<span style="color: #2040a0">SqlServer</span>.<span style="color: #2040a0">Currency</span> <span style="color: #2040a0">ConvertCurrency</span><span style="color: #4444FF">(</span><span style="color: #2040a0">SqlMoney</span> <span style="color: #2040a0">fromAmount</span>, <span style="color: #2040a0">SqlString</span> <span style="color: #2040a0">toCultureName</span><span style="color: #4444FF">)</span>
		<span style="color: #4444FF"><strong>{</strong></span>
            <span style="color: #2040a0">CultureInfo</span> <span style="color: #2040a0">toCulture</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">CultureInfo</span>.<span style="color: #2040a0">CreateSpecificCulture</span><span style="color: #4444FF">(</span><span style="color: #2040a0">toCultureName</span>.<span style="color: #2040a0">Value</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

            <span style="color: #0000FF"><strong>if</strong></span> <span style="color: #4444FF">(</span><span style="color: #2040a0">toCulture</span>.<span style="color: #2040a0">Equals</span><span style="color: #4444FF">(</span><span style="color: #2040a0">USCulture</span><span style="color: #4444FF">)</span><span style="color: #4444FF">)</span>
			<span style="color: #4444FF"><strong>{</strong></span>
				<span style="color: #2040a0">Currency</span> <span style="color: #2040a0">c</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">Currency</span><span style="color: #4444FF">(</span><span style="color: #2040a0">USCulture</span>, <span style="color: #4444FF">(</span><span style="color: #2040a0">decimal</span><span style="color: #4444FF">)</span><span style="color: #2040a0">fromAmount</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
				<span style="color: #0000FF"><strong>return</strong></span> <span style="color: #2040a0">c</span><span style="color: #4444FF">;</span>
			<span style="color: #4444FF"><strong>}</strong></span>

			<span style="color: #2040a0">String</span> <span style="color: #2040a0">toCurrencyCode</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">RegionInfo</span><span style="color: #4444FF">(</span><span style="color: #2040a0">toCulture</span>.<span style="color: #2040a0">LCID</span><span style="color: #4444FF">)</span>.<span style="color: #2040a0">ISOCurrencySymbol</span><span style="color: #4444FF">;</span>

            <span style="color: #008000">// Find the rate closest to today</span>
            
            <span style="color: #2040a0">SqlCommand</span> <span style="color: #2040a0">command</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">SqlContext</span>.<span style="color: #2040a0">GetConnection</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span>.<span style="color: #2040a0">CreateCommand</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">command</span>.<span style="color: #2040a0">CommandType</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">CommandType</span>.<span style="color: #2040a0">StoredProcedure</span><span style="color: #4444FF">;</span>         
            <span style="color: #2040a0">command</span>.<span style="color: #2040a0">CommandText</span> <span style="color: #4444FF">=</span> <span style="color: #444444">&quot;usp_LookupConversionRate&quot;</span><span style="color: #4444FF">;</span>

            <span style="color: #2040a0">SqlParameter</span> <span style="color: #2040a0">onDateParameter</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlParameter</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;@OnDate&quot;</span>, <span style="color: #2040a0">SqlDbType</span>.<span style="color: #2040a0">DateTime</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">onDateParameter</span>.<span style="color: #2040a0">Value</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">DateTime</span>.<span style="color: #2040a0">Now</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">command</span>.<span style="color: #2040a0">Parameters</span>.<span style="color: #2040a0">Add</span><span style="color: #4444FF">(</span><span style="color: #2040a0">onDateParameter</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

            <span style="color: #2040a0">SqlParameter</span> <span style="color: #2040a0">toCurrencyCodeParameter</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlParameter</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;@ToCurrencyCode&quot;</span>, <span style="color: #2040a0">SqlDbType</span>.<span style="color: #2040a0">NChar</span>, <span style="color: #FF0000">3</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">toCurrencyCodeParameter</span>.<span style="color: #2040a0">Value</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">toCurrencyCode</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">command</span>.<span style="color: #2040a0">Parameters</span>.<span style="color: #2040a0">Add</span><span style="color: #4444FF">(</span><span style="color: #2040a0">toCurrencyCodeParameter</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

            <span style="color: #2040a0">SqlParameter</span> <span style="color: #2040a0">resultParameter</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlParameter</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;@Result&quot;</span>, <span style="color: #2040a0">SqlDbType</span>.<span style="color: #2040a0">Decimal</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">resultParameter</span>.<span style="color: #2040a0">Precision</span> <span style="color: #4444FF">=</span> <span style="color: #FF0000">10</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">resultParameter</span>.<span style="color: #2040a0">Scale</span> <span style="color: #4444FF">=</span> <span style="color: #FF0000">4</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">resultParameter</span>.<span style="color: #2040a0">Direction</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">ParameterDirection</span>.<span style="color: #2040a0">Output</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">command</span>.<span style="color: #2040a0">Parameters</span>.<span style="color: #2040a0">Add</span><span style="color: #4444FF">(</span><span style="color: #2040a0">resultParameter</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

            <span style="color: #2040a0">command</span>.<span style="color: #2040a0">ExecuteNonQuery</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

            <span style="color: #2040a0">decimal</span> <span style="color: #2040a0">conversionFactor</span><span style="color: #4444FF">;</span>


            <span style="color: #0000FF"><strong>if</strong></span> <span style="color: #4444FF">(</span><span style="color: #2040a0">resultParameter</span>.<span style="color: #2040a0">Value</span> <span style="color: #2040a0">is</span> <span style="color: #2040a0">decimal</span><span style="color: #4444FF">)</span>
            <span style="color: #4444FF"><strong>{</strong></span>
                <span style="color: #2040a0">conversionFactor</span> <span style="color: #4444FF">=</span> <span style="color: #4444FF">(</span><span style="color: #2040a0">decimal</span><span style="color: #4444FF">)</span><span style="color: #4444FF">(</span><span style="color: #2040a0">resultParameter</span>.<span style="color: #2040a0">Value</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #4444FF"><strong>}</strong></span>
            <span style="color: #0000FF"><strong>else</strong></span>
            <span style="color: #4444FF"><strong>{</strong></span>
                <span style="color: #2040a0">conversionFactor</span> <span style="color: #4444FF">=</span> <span style="color: #FF0000">1.</span>0<span style="color: #2040a0">M</span><span style="color: #4444FF">;</span>
                <span style="color: #2040a0">toCulture</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">USCulture</span><span style="color: #4444FF">;</span>
            <span style="color: #4444FF"><strong>}</strong></span>

            <span style="color: #0000FF"><strong>return</strong></span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">Currency</span><span style="color: #4444FF">(</span><span style="color: #2040a0">toCulture</span>, <span style="color: #4444FF">(</span><span style="color: #4444FF">(</span><span style="color: #2040a0">decimal</span><span style="color: #4444FF">)</span><span style="color: #2040a0">fromAmount</span> <span style="color: #4444FF">*</span> <span style="color: #2040a0">conversionFactor</span><span style="color: #4444FF">)</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
		<span style="color: #4444FF"><strong>}</strong></span>
	<span style="color: #4444FF"><strong>}</strong></span>
<span style="color: #4444FF"><strong>}</strong></span>
</pre>
